package com.perritosca.webservice;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;


/**
 * Simple DB helper to encapsulate all SQLite functionalities No model
 * dependency, just set the initial vars @ TAG string to send to the log @
 * DATABASE_NAME (obvious) @ DATABASE_CREATE sql statement to create a new
 * database if it not exists @ DATABASE_VERSION to force an update and execute
 * the new sql @DATABASE_CREATE @ DATABASE_TABLE_* enum for the table names
 */
public class DBHelper {
	private static final String TAG = "APP RESERVAS - DbHelper";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	/**
	 * Database creation sql statement
	 */
	public static final String DB_TABLE_REST = "Restaurant";
	public static final String DB_TABLE_CATEGORY = "Category";
	public static final String DB_TABLE_SUBCATEGORY = "SubCategory";
	public static final String DB_TABLE_REST_SUBCAT = "RestaurantSubCategory";

	public static final String KEY_ROWID = "_id";
	private static final int DATABASE_VERSION = 6;

	private static Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		private String DB_PATH = Environment.getDataDirectory()
				+ "/data/com.perritosca.webservice/databases/";
		private static final String DB_NAME = "AppReservas";
		private static final String DB_ASSET_NAME = "AppReservas.sqlite";
		private SQLiteDatabase myDataBase;
		private final Context ctx;

		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, DATABASE_VERSION);
			ctx = context;
		}

		// public SQLiteDatabase openDataBase() throws SQLException {
		//
		// // Open the database
		// String myPath = DB_PATH + DB_NAME;
		// return SQLiteDatabase.openDatabase(myPath, null,
		// SQLiteDatabase.OPEN_READONLY);
		//
		// }

		public SQLiteDatabase onCreateDatabase() {
			boolean dbExist = false;

			if (!dbExist) {
				myDataBase = this.getWritableDatabase();
				Log.d("-------------------", "NO EXISTE DB");
				try {
					copyDataBase();
					Log.d("-------------------", "DB copiada");
				} catch (IOException e) {
					// throw new Error("Error copying database");
					Log.d("-------------------", "ERROR COPIANDO DB");
					e.printStackTrace();
				}
			}else{
				myDataBase = this.getWritableDatabase();

			}

			/*if (!ReservasApplication.getInstance().isCreated())
				ReservasApplication.getInstance().dataLoaded();
			ReservasApplication.getInstance().setCreated(true);
*/
			return myDataBase;
		}

		private boolean checkDataBase() {

			String myPath = DB_PATH + DB_NAME;
			File dbFile = new File(DB_PATH + DB_NAME);
			return dbFile.exists();

		}

		private void copyDataBase() throws IOException {

			// Open your local db as the input stream
			InputStream myInput = ctx.getAssets().open(DB_ASSET_NAME);

			// Path to the just created empty db
			String outFileName = DB_PATH + DB_NAME;

			// Open the empty db as the output stream
			OutputStream myOutput = new FileOutputStream(outFileName);

			// transfer bytes from the inputfile to the outputfile
			byte[] buffer = new byte[1024];
			int length;
			while ((length = myInput.read(buffer)) > 0) {
				myOutput.write(buffer, 0, length);
			}

			// Close the streams
			myOutput.flush();
			myOutput.close();
			myInput.close();
		}

		@Override
		public synchronized void close() {
			if (myDataBase != null)
				myDataBase.close();
			super.close();
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// Log.d("-------------------", "ACTUALIZANDO DB");
			// if (oldVersion < newVersion) {
			// try {
			// ctx.deleteDatabase(DB_NAME);
			// myDataBase = this.getReadableDatabase();
			// Log.d("----------", "PATH:"
			// + myDataBase.getPath().toString());
			// myDataBase.close();
			// copyDataBase();
			// myDataBase.setVersion(newVersion);
			//
			// Log.d("-------------------", "DB copiada");
			// } catch (IOException e) {
			// // throw new Error("Error copying database");
			// Log.d("-------------------", "ERROR COPIANDO DB");
			// e.printStackTrace();
			// }
			// }
		}
	}

	public SQLiteDatabase getDB() {
		return mDb;
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx
	 *            the Context within which to work
	 */
	public DBHelper(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the notes database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException
	 *             if the database could be neither opened or created
	 */
	public DBHelper open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);

		mDb = mDbHelper.onCreateDatabase();

		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	/**
	 * Create a new record using the data provided. If the record is
	 * successfully created return the new rowId for that note, otherwise return
	 * a -1 to indicate failure.
	 * 
	 * @param DB_TABLE
	 *            name of the table to fetch
	 * @param initialValues
	 *            info to insert
	 * @return rowId or -1 if failed
	 */
	public long insert(String DB_TABLE, ContentValues initialValues) {
		return mDb.insert(DB_TABLE, null, initialValues);
	}

	/**
	 * Update the record using the details provided. The record to be updated is
	 * specified using the rowId, and it is altered to use the data values
	 * passed in args variable
	 * 
	 * @param DB_TABLE
	 *            name of the table to fetch
	 * @param args
	 *            info to update
	 * @param rowId
	 *            id of note to update
	 * @return true if the note was successfully updated, false otherwise
	 */
	public boolean update(String DB_TABLE, ContentValues args, String rowId) {
		return mDb.update(DB_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
	}

	public boolean update(String DB_TABLE, ContentValues args, String field,
			String value) {
		return mDb.update(DB_TABLE, args, field + "=" + value, null) > 0;
	}

	/**
	 * Delete the record with the given rowId
	 * 
	 * @param DB_TABLE
	 *            name of the table to fetch
	 * @param rowId
	 *            id of the record to delete
	 * @return true if deleted, false otherwise
	 */
	public boolean delete(String DB_TABLE, String rowId) {
		return mDb.delete(DB_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 * Return a Cursor over the list of all records in the database table
	 * 
	 * @param DB_TABLE
	 *            name of the table to fetch
	 * @param fields
	 *            fields to return
	 * @return Cursor over all notes
	 */
	public Cursor findAll(boolean distinct, String DB_TABLE, String[] fields) {
		return mDb.query(distinct, DB_TABLE, fields, null, null, null, null,
				null, null);
	}

	public Cursor findAll(String DB_TABLE, String[] fields) {
		return mDb.query(DB_TABLE, fields, null, null, null, null, null);
	}

	public Cursor findAll(String DB_TABLE, String[] fields, String fieldOrder,
			String order) {
		return mDb.query(DB_TABLE, fields, null, null, null, null, fieldOrder
				+ " " + order);
	}

	public Cursor findAll(String DB_TABLE, String[] fields, String filters,
			String fieldOrder, String order) {
		return mDb.query(DB_TABLE, fields, filters, null, null, null,
				fieldOrder + " " + order);
	}

	public Cursor findAllWhere(String DB_TABLE, String[] fields, String field,
			String value, String fieldOrder, String order, String idState) {
		StringBuffer selection = new StringBuffer("idState=");
		selection.append(idState).append(" ").append("AND ")
				.append(field + "='" + value + "'").toString();
		return mDb.query(DB_TABLE, fields, selection.toString(), null, null,
				null, fieldOrder + " " + order);
	}

	public Cursor findAllFavCategory(String DB_TABLE, String[] fields,
			String field, String value, String fieldOrder, String order) {
		return mDb.query(DB_TABLE, fields, field + "='" + value
				+ "' AND Favorito='true'", null, null, null, fieldOrder + " "
				+ order);
	}

	public Cursor findAllGrouped(String DB_TABLE, String[] fields,
			String fieldGrouped, String field, String value) {
		return mDb.query(DB_TABLE, fields, field + "='" + value + "'", null,
				fieldGrouped, null, fieldGrouped + " ASC");
	}

	public Cursor findAllGrouped(String DB_TABLE, String[] fields,
			String fieldGrouped) {
		return mDb.query(DB_TABLE, fields, null, null, fieldGrouped, null,
				fieldGrouped + " ASC");
	}

	/**
	 * Return a Cursor positioned at the record that matches the given rowId
	 * 
	 * @param DB_TABLE
	 *            name of the table to fetch
	 * @param fields
	 *            fields to return
	 * @param rowId
	 *            id of note to retrieve
	 * @return Cursor positioned to matching note, if found
	 * @throws SQLException
	 *             if note could not be found/retrieved
	 */
	public Cursor find(String DB_TABLE, String[] fields, String rowId)
			throws SQLException {
		if (!mDb.isOpen()) {
			mDb = mDbHelper.getReadableDatabase();
		}
		Cursor mCursor = mDb.query(true, DB_TABLE, fields, KEY_ROWID + "="
				+ rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	public Cursor find(String DB_TABLE, String[] fields, String findField,
			String rowId) throws SQLException {

		Cursor mCursor = mDb.query(true, DB_TABLE, fields, findField + "="
				+ rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	/**
	 * Return a Cursor positioned at the record that matches the given rowId
	 * 
	 * @param DB_TABLE
	 *            name of the table to fetch
	 * @param fields
	 *            fields to return
	 * @param field
	 *            key for the field to filter on
	 * @param value
	 *            string to search
	 * @return Cursor positioned to matching note, if found
	 * @throws SQLException
	 *             if note could not be found/retrieved
	 */
	public Cursor search(String DB_TABLE, String[] fields, String field,
			String value) throws SQLException {

		if (!value.contains("\""))
			value = "\"" + value + "\"";
		if (!mDb.isOpen()) {
			mDb = mDbHelper.getReadableDatabase();
		}
		/*Cursor mCursor = mDb.query(true, DB_TABLE, fields, field + "=" + value,
				null, null, null, RestaurantHelper.KEY_NAME + " ASC", null);
		*/
		Cursor mCursor = mDb.query(true, DB_TABLE, fields, field + "=" + value,
				null, null, null, " ASC", null);
		
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	/*public Cursor search(String DB_TABLE, String[] fields, String field,
			String categoryId, String value) {
		if (!value.contains("\""))
			value = "\"" + value + "\"";
		Cursor mCursor = mDb.query(true, DB_TABLE, fields, field + "=" + value
				+ " AND " + RestaurantHelper.KEY_SUBCOSTUMERID
				+ " IN  (select " + RestaurantHelper.KEY_SUBCOSTUMERID
				+ " from RestaurantSubcategory where "
				+ SubcategoryHelper.KEY_SUBCATEGORYID + "=" + categoryId + ")",
				null, null, null, RestaurantHelper.KEY_NAME + " ASC", null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}*/

	public Cursor search(String DB_TABLE, String[] fields, String field,
			String value, String fieldOrder, String order) throws SQLException {

		Cursor mCursor = mDb.query(true, DB_TABLE, fields, field + "=" + value,
				null, null, null, null, fieldOrder + " " + order);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	/**
	 * Return a Cursor positioned at the record that matches the given rowId
	 * 
	 * @param DB_TABLE
	 *            name of the table to fetch
	 * @param fields
	 *            fields to return
	 * @param rowId
	 *            id of note to retrieve
	 * @return true if exists, else false
	 * @throws SQLException
	 *             if note could not be found/retrieved
	 */
	public boolean exists(String DB_TABLE, String[] fields, String rowKey,
			String rowId) throws SQLException {

		Cursor mCursor = mDb.query(true, DB_TABLE, fields,
				rowKey + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			if (mCursor.getCount() > 0) {
				return true;
			}
		}
		return false;

	}

}
